<?php

/*
 * This class is used for as Model for Request table 
 */

class Model_Request extends Model_Base {

    protected $_name = 'tbl_request';
    protected $_primaryKey = 'req_id';

    
    //get company array
    public function getcompanyArray($where = '') {
        $companyModel = new Model_Company();
        $companyArray = $companyModel->fetchCompany($where);
        $finalArray[''] = '';
        foreach ($companyArray as $com) {
            $finalArray[$com['com_id']] = $com['com_name'];
        }
        return $finalArray;
    }
    
    
    //get customer data
    public function getcustomerArray($lang_id) {
       
        $customerModel = new Model_Customer();
        $customerArray = $customerModel->fetchCustomer($lang_id);
        
        $finalArray[''] = '';
        foreach ($customerArray as $com) {
            $finalArray[$com['cust_id']] = ucfirst($com['cust_firstname']) . " " . ucfirst($com['cust_lastname']);
        }
        return $finalArray;
    }

    //get site data
    public function getsiteArray($where = "") {
        $siteModel = new Model_Site();
        $siteArray = $siteModel->fetchSite($where);
        $finalArray[''] = '';
        if($where !=''){
            foreach ($siteArray as $com) {
                $finalArray[$com['site_id']] = ucfirst($com['site_name']);
            }
        }
        return $finalArray;
    }

    //get subject data
    public function getsubjectArray($where = "") {
        $subjectModel = new Model_Subject();
        $subjectArray = $subjectModel->fetchSubject($where);
        $finalArray[''] = '';
        if($where != ''){
            foreach ($subjectArray as $com) {
                $finalArray[$com['subj_id']] = ucfirst($com['subj_name']);
            }
        }
        return $finalArray;
    }

    /**
     * Function to return different status of the Request
     *
     * @param  null
     * @return null|Zend_Db_Table_Row_Abstract
     */
    public function getStatusArray($lang_id) {
        $ParentdropdownModel = new Model_Parentlist();
        $ParentdropdownArray = $ParentdropdownModel->fetchall('Request Status');
        #$finalArray[''] = '-- Select Status --';
        $parent_id = $ParentdropdownArray[0]['parent_id'];
        $ChilddropdownModel = new Model_List();
        $where = 'child.lang_id = '.$lang_id.' and child.parent_id = '.$parent_id.' ';
        $ChilddropdownArray = $ChilddropdownModel->fetchType($where);

    
        foreach ($ChilddropdownArray as $com) {
            $finalArray[$com['child_id']] = $com['child_name'];
        }
        if (!empty($finalArray)) {
            return $finalArray;
        } else {
            return array();
        }
        
        
        /*return array(
            '' => '-- Select Status --',
            '1' => 'Active',
            '0' => 'Inactive'
        );*/
    }

    /**
     * Fetch an individual entry for view
     *
     * @param  int|string $id
     * @return null|Zend_Db_Table_Row_Abstract
     */
    public function fetchEntryByViewId($id,$lang_id) {

        $select = $this->select()
                ->setIntegrityCheck(false)
                ->from(array('request' => 'tbl_request'))
                ->join(array('lgn' => 'tbl_login'), "lgn.lgn_id = request.updated_lgn_id")
                ->join(array('com' => 'tbl_company'), "com.com_id = request.req_com_id",array('com_name'))
                ->join(array('site' => 'tbl_site'), "site.site_id = request.site_id")
                ->join(array('subj' => 'tbl_subject'), "subj.subj_id = request.subj_id")
                ->joinLeft(array('child' => 'tbl_list'), "child.child_id = request.req_status and child.lang_id =".$lang_id."",array('req_status_name' => 'child_name'))
                ->where('req_id = ?', $id)
                ->where('req_status != 2');
        // see reasoning in fetchEntries() as to why we return only an array
       # echo $select;exit;
        $data = $this->fetchRow($select);
        if (!empty($data)) {
            return $data->toArray();
        } else {
            return array();
        }
    }

    public function fetchEntryById($id) {
        $select = $this->select()
                ->from(array('request' => 'tbl_request'))
                ->setIntegrityCheck(false)
                ->where('req_id = ?', $id)
                ->where('req_status != 2');
        // see reasoning in fetchEntries() as to why we return only an array

        $data = $this->fetchRow($select);
        if (!empty($data)) {
            return $data->toArray();
        } else {
            return array();
        }
    }

    /**
     *  This function is used to get requests
     * @return null|Zend_Db_Table_Row_Abstract
     */
    public function fetchRequest($where = "", $limit = "", $offset = "0", $order = 'req_id', $sort = 'ASC') {
      // echo $where;exit;
        $select = $this->select()
                ->from(array('request' => 'tbl_request'))
                ->setIntegrityCheck(false)
                ->join(array('site' => 'tbl_site'), "site.site_id = request.site_id")
                ->join(array('subj' => 'tbl_subject'), "subj.subj_id = request.subj_id")
                ->join(array('com' => 'tbl_company'), "com.com_id = request.req_com_id")
                ->join(array('child' => 'tbl_list'), "child.child_id = request.req_status",array('req_status_name' => 'child_name'))
                ->where('req_status != 2');
        if ($where != '') {
          //  echo 'isn';exit;
            $select->where($where);
        }
        if ($limit != "") {
            $select->limit($limit, $offset);
        }

        $select->order($order . " " . $sort);
      #  echo $select; exit;
        $data = parent::fetchAll($select);
        if (!empty($data)) {
            return $data->toArray();
        } else {
            return array();
        }
    }

    /**
     *  This function is used to get total orders
     * @return int
     */
    public function getRequestCount($where) {
        $select = $this->select()
                ->from(array('request' => 'tbl_request'), Array('cnt' => 'count(*)'))
                ->setIntegrityCheck(false)
                ->join(array('site' => 'tbl_site'), "site.site_id = request.site_id")
                ->join(array('subj' => 'tbl_subject'), "subj.subj_id = request.subj_id")
                ->join(array('com' => 'tbl_company'), "com.com_id = request.req_com_id")
                ->join(array('child' => 'tbl_list'), "child.child_id = request.req_status",array('req_status_name' => 'child_name'))
               ->where('req_status != 2');
        if ($where != '') {
            $select->where($where);
        }
        $rows = parent::fetchRow($select);
        return($rows->cnt);
    }

    /**
     *  This function is used to delete requests
     * @return null|Zend_Db_Table_Row_Abstract
     */
    public function deleteRequest($prdtArray) {

        if (is_array($prdtArray)) {
            foreach ($prdtArray as $_request) {
                $requestData = Array();
                $requestData = $this->fetchEntryById($_request);
                if (count($requestData) > 0) {
                    $data = array('req_status' => '2');
                    $this->update($data, 'req_id =' . (int) $_request);
                }
            }
        } else {
            $req_id = $prdtArray;
            $requestData = $this->fetchEntryById($req_id);
            if (count($requestData) > 0) {
                $data = array('req_status' => '2');
                $this->update($data, 'req_id =' . $req_id);
            }
        }
    }
    
    //request with assignrequest
    
    public function fetchRequestWithassign($where = "", $limit = "", $offset = "0", $order = 'request.req_id', $sort = 'ASC') {
        $select = $this->select()
                ->from(array('request' => 'tbl_request'))
                ->setIntegrityCheck(false)
                //->join(array('lgn' => 'tbl_login'), "lgn.lgn_id = request.updated_lgn_id")
                ->joinLeft(array('cn' => 'tbl_contractor'), "request.updated_lgn_id = cn.lgn_id",array('cont_name'=>"concat(cn.cont_firstname,' ',cn.cont_lastname)"))
                ->joinLeft(array('cust' => 'tbl_customer'), "request.updated_lgn_id = cust.lgn_id",array('cust_name'=>"concat(cust.cust_firstname,' ',cust.cust_lastname)"))
                ->joinLeft(array('adm' => 'tbl_admin'), "request.updated_lgn_id = adm.lgn_id",array('adm_name'=>"concat(adm.adm_firstname,' ',adm.adm_lastname)"))
                ->joinLeft(array('ass' => 'tbl_request_assign') , "ass.ass_req_id = request.req_id")
                ->joinLeft(array('cont' => 'tbl_contractor'), "ass.ass_lgn_id = cont.lgn_id",array('cont.cont_firstname','cont.cont_lastname'))
                ->joinLeft(array('cust1' => 'tbl_customer'), "ass.ass_lgn_id = cust1.lgn_id",array('cust1.cust_firstname','cust1.cust_lastname'))
                ->join(array('site' => 'tbl_site'), "site.site_id = request.site_id")
                ->join(array('subj' => 'tbl_subject'), "subj.subj_id = request.subj_id")
                ->join(array('com' => 'tbl_company'), "com.com_id = request.req_com_id")
                ->where('req_status != 2');
        if ($where != '') {
          //  echo 'isn';exit;
            $select->where($where);
        }
        if ($limit != "") {
            $select->limit($limit, $offset);
        }

        $select->group('request.req_id');
        $select->order($order . " " . $sort);
        #echo $select; exit;
        $data = parent::fetchAll($select);
        if (!empty($data)) {
            return $data->toArray();
        } else {
            return array();
        }
    }

    public function getRequestWithassignCount($where) {
        $select = $this->select()
                ->from(array('request' => 'tbl_request'), Array('cnt' => 'count(*)'))
                ->setIntegrityCheck(false)
                ->joinLeft(array('cn' => 'tbl_contractor'), "request.updated_lgn_id = cn.lgn_id",array('cont_name'=>"concat(cn.cont_firstname,' ',cn.cont_lastname)"))
                ->joinLeft(array('cust' => 'tbl_customer'), "request.updated_lgn_id = cust.lgn_id",array('cust_name'=>"concat(cust.cust_firstname,' ',cust.cust_lastname)"))
                ->joinLeft(array('adm' => 'tbl_admin'), "request.updated_lgn_id = adm.lgn_id",array('adm_name'=>"concat(adm.adm_firstname,' ',adm.adm_lastname)"))
                ->joinLeft(array('ass' => 'tbl_request_assign'), "ass.ass_req_id = request.req_id")
                ->joinLeft(array('cont' => 'tbl_contractor'), "ass.ass_lgn_id = cont.cont_id")
                ->join(array('site' => 'tbl_site'), "site.site_id = request.site_id")
                ->join(array('subj' => 'tbl_subject'), "subj.subj_id = request.subj_id")
                ->join(array('com' => 'tbl_company'), "com.com_id = request.req_com_id")
                ->where('req_status != 2');
        if ($where != '') {
            $select->where($where);
        }
        $rows = parent::fetchRow($select);
        return($rows->cnt);
    }

}
